﻿using System;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DataAccess
{
    public class HoaDonDAO
    {
        private static readonly String URL = ConfigurationSettings.AppSettings["Main.ConnectionString"];
        public HoaDonDAO()
        {

        }

        public static DataSet GetAll()
        {
            return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT * FROM HoaDon");
        }
        public static DataSet GetDetail(string mahoadon,int loaihoadon)
        {
            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@MaHoaDon", mahoadon);
            sqlParameter[1] = new SqlParameter("@LoaiHoaDon", loaihoadon);
    
            return SqlHelper.ExecuteDataset(URL, CommandType.StoredProcedure, "SELECT_DETAIL", sqlParameter);

        }
        public static DataSet Search(string mahoadon,int loaihoadon)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MaHoaDon", mahoadon);
            string query = "";
            if (loaihoadon == 1)
            {
                query = " LoaiHoaDon = 1";
            }
            else if (loaihoadon == 2)
            {
                query = " LoaiHoaDon = 2";
            }

            if (mahoadon == "" && loaihoadon == 0)
            {
                return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT * FROM HoaDon");
            }
            else if (mahoadon == "" && loaihoadon != 0)
            {
                return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT * FROM HoaDon " + (loaihoadon == 0 ? "" : " WHERE ") + query);
            }
            else
            {
                return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT * FROM HoaDon WHERE MaHoaDon = @MaHoaDon" + (loaihoadon == 0 ? "" : " AND ") + query, sqlParameter);
            }
            
        }
        public static void Delete(int id) 
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MaHoaDon", id);
            SqlHelper.ExecuteNonQuery(URL, CommandType.Text, "DELETE FROM HoaDon WHERE MaHoaDon = @MaHoaDon", sqlParameter);
        }
        public static DataSet GetReport()
        {
            return SqlHelper.ExecuteDataset(URL, CommandType.StoredProcedure,"REPORT_HOADON");
        }
        public static int Insert(double tongtien, double tientra, double tienno,int loaihoadon,string ghichu)
        {
            SqlParameter[] sqlParameter = new SqlParameter[5];
            sqlParameter[0] = new SqlParameter("@TongTien", tongtien);
            sqlParameter[1] = new SqlParameter("@TienTra", tientra);
            sqlParameter[2] = new SqlParameter("@TienNo", tienno);
            sqlParameter[3] = new SqlParameter("@LoaiHoaDon", loaihoadon);
            sqlParameter[4] = new SqlParameter("@GhiChu", ghichu);
            SqlHelper.ExecuteNonQuery(URL, CommandType.Text, "INSERT INTO HoaDon(TongTien,TienTra,TienNo,LoaiHoaDon,GhiChu) VALUES(@TongTien,@TienTra,@TienNo,@LoaiHoaDon,@GhiChu)", sqlParameter);
            return (int)SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT TOP 1 MaHoaDon FROM HoaDon ORDER BY MaHoaDon DESC").Tables[0].Rows[0]["MaHoaDon"];
        }

        public static void Update(int id,double tongtien, double tientra, double tienno, int loaihoadon, string ghichu)
        {
            SqlParameter[] sqlParameter = new SqlParameter[6];
            sqlParameter[0] = new SqlParameter("@TongTien", tongtien);
            sqlParameter[1] = new SqlParameter("@TienTra", tientra);
            sqlParameter[2] = new SqlParameter("@TienNo", tienno);
            sqlParameter[3] = new SqlParameter("@LoaiHoaDon", loaihoadon);
            sqlParameter[4] = new SqlParameter("@GhiChu", ghichu);
            sqlParameter[4] = new SqlParameter("@MaHoaDon", id);
            SqlHelper.ExecuteNonQuery(URL, CommandType.Text, "UPDATE HoaDon SET TongTien= @TongTien,TienTra = @TienTra,TienNo = @TienNo,LoaiHoaDon = @LoaiHoaDon,GhiChu = @GhiChu WHERE MaHoaDon = @MaHoaDon", sqlParameter);
        }
        public static DataSet GetBy(int id)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MaHoaDon", id);
            return SqlHelper.ExecuteDataset(URL, CommandType.Text, "SELECT * FROM HoaDon WHERE MaHoaDon = @MaHoaDon", sqlParameter);
        }
    }
}
